//
// Created by henry on 2025/11/7.
//

#include "database.h"

//定义数据库句柄
sqlite3 * db;

//打开数据库
int open_db(){
    int rc = sqlite3_open("bookshop.db",&db);
    if(rc!=SQLITE_OK){
        fprintf(stderr,"open_db:创建数据库失败:%s\n", sqlite3_errmsg(db));
        return -1;
    }
    printf("open_db:创建数据库成功!\n");
    return 0;
}
//初始数据库，建表
int init_db(){
    //打开数据库
    if(open_db()!=SQLITE_OK){
        return -1;
    }
    //建表
    if(create_front_user()!=0){
        return -1;
    }

    if(create_admin_user()!=0){
        return -1;
    }

    if(create_book_category()!=0){
        return -1;
    }

    if(create_book()!=0){
        return -1;
    }

    printf("init_db:数据库初始化完成!\n");
    return 0;
}

//创建前台用户表
int create_front_user(){
//    const char * drop =
//            "drop table front_user;";
    //定义建表语句
    const char * sql =
            "drop table if EXISTS front_user;"
            "create table if not exists front_user"
            "("
            "   id integer primary key,"
            "   username text,"
            "   password text,"
            "   phone text,"
            "   nikename text,"
            "   address text"
            ");"
            "insert into front_user"
            "(username,password,phone,nikename,address)"
            "values"
            "('test','test','13112345678','大白','河南郑州');";
    //执行sql
    char * errmsg;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if(rc!=SQLITE_OK){
        fprintf(stderr,"create_front_user:建表出错:%s\n",errmsg);
        return -1;
    }
    printf("create_front_user:建表成功!\n");
    return 0;
}
//创建管理员账号表
int create_admin_user(){
    //定义建表语句
    const char * sql =
            "drop table if EXISTS admin_user;"
            "create table if not exists admin_user"
            "("
            "   id integer primary key,"
            "   account text,"
            "   password text,"
            "   status text default 0"
            ");"
            "insert into admin_user"
            "(account,password)"
            "values"
            "('admin','admin');";
    //执行sql
    char * errmsg;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if(rc!=SQLITE_OK){
        fprintf(stderr,"create_admin_user:建表出错:%s\n",errmsg);
        return -1;
    }
    printf("create_admin_user:建表成功!\n");
    return 0;
}
//创建图书分类表
int create_book_category(){
    //定义建表语句
    const char * sql =
            "drop table if EXISTS book_category;"
            "create table if not exists book_category"
            "("
            "   category_id integer primary key,"
            "   category_name text"
            ");"
            "insert into book_category"
            "(category_name)"
            "values"
            "('科幻'),('文艺'),('经济');";
    //执行sql
    char * errmsg;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if(rc!=SQLITE_OK){
        fprintf(stderr,"create_book_category:建表出错:%s\n",errmsg);
        return -1;
    }
    printf("create_book_category:建表成功!\n");
    return 0;
}
//创建图书信息表
int create_book(){
    //定义建表语句
    const char * sql =
            "drop table if EXISTS book;"
            "create table if not exists book"
            "("
            "   id integer primary key,"
            "   book_name text,"
            "   author text,"
            "   price integer,"
            "   publish text,"
            "   publish_date text,"
            "   category_id integer"
            ");"
            "insert into book "
            "(book_name,author,price,publish,publish_date,category_id) "
            "values "
            "('小白','张三',20,'机械出版社',date(),1),"
            "('小灰','李四',10,'机械出版社',date(),2);";
    //执行sql
    char * errmsg;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if(rc!=SQLITE_OK){
        fprintf(stderr,"create_book_category:建表出错:%s\n",errmsg);
        return -1;
    }
    printf("create_book_category:建表成功!\n");
    return 0;
}

//关闭数据库
int close_db(){
    if(db!=NULL){
        sqlite3_close(db);
        db=NULL;
    }
    printf("close_db:关闭数据库成功!\n");
    return 0;
}
